/***************************************************************
*  Copyright (c) 2007 by GroupMe! Team (www.groupme.net)
*  All rights reserved.
*
*  This file is part of the GroupMe! Project. Source code of 
*  this project is closed and redistribution of this code is
*  prohibited. 
*  
*  Contact: http://www.groupme.net
*
*  This copyright notice MUST APPEAR in all copies of the file!
***************************************************************/
package net.groupme.search.strategy;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Iterator;
import java.util.List;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import net.groupme.exceptions.UserNotExistingException;
import net.groupme.model.MODEL_CONSTANTS;
import net.groupme.model.ObjectFactory;
import net.groupme.model.Tag;
import net.groupme.model.dao.DATABASE_CONSTANTS;
import net.groupme.search.strategy.model.PreferencesInTags;
import net.groupme.search.strategy.model.RankingPreferences;
import net.groupme.search.strategy.model.WeightedIdentifiableComparatorByWeight;
import net.groupme.search.strategy.model.WeightedResource;
import net.groupme.search.strategy.model.WeightedTag;
import net.groupme.search.strategy.model.WeightedUser;
import net.groupme.utils.sql.GetWeightedResource;

/**
 * created on Jan 20, 2009
 * 
 * This class implements the GRank algorithm.
 * 
 * @author Fabian Abel, <a href="mailto:abel@l3s.de">abel@l3s.de</a>
 * @author last edited by: $Author: fabian $
 * 
 * @version $Revision: 1.1 $ $Date: 2009-01-20 18:47:45 $
 */
public class GRank implements RankingStrategy {

	/** Logger for this class and subclasses */
	protected final  Log logger = LogFactory.getLog(this.getClass());
	
	/* (non-Javadoc)
	 * @see net.groupme.search.strategy.RankingStrategy#getDescription()
	 */
	public String getDescription() {
		return "GRank algorithm";
	}

	/* (non-Javadoc)
	 * @see net.groupme.search.strategy.RankingStrategy#getName()
	 */
	public String getName() {
		return "GRank";
	}

	/* (non-Javadoc)
	 * @see net.groupme.search.strategy.RankingStrategy#rankResources(net.groupme.search.strategy.model.RankingPreferences)
	 */
	public List<WeightedResource> rankResources(RankingPreferences preferences) {
		List<WeightedResource> ranking = new ArrayList<WeightedResource>();
		List<WeightedResource> actualRanking = null;
		for(Tag tag: (PreferencesInTags)preferences){
			actualRanking = rankResources(tag);
			for (WeightedResource weightedResource : actualRanking) {
				if(ranking.contains(weightedResource)){
					ranking.get(ranking.indexOf(weightedResource)).setWeight(ranking.get(ranking.indexOf(weightedResource)).getWeight() + weightedResource.getWeight());
				}else{
					ranking.add(weightedResource);
				}
			}
		}
		Collections.sort(ranking, new WeightedIdentifiableComparatorByWeight());
		return ranking;
	}
	
	@SuppressWarnings("unchecked")
	protected List<WeightedResource> rankResources(Tag tag){
		//Number of Users who tagged Resource X directly with tag:
		String query = 
			"SELECT tas." + DATABASE_CONSTANTS.USERTAGGROUPRESOURCE_RESOURCEID + ", " +
					"r." + DATABASE_CONSTANTS.RESOURCE_NAME + ", " +
 					"r." + DATABASE_CONSTANTS.RESOURCE_DESCRIPTION + ", " +
 					"r." + DATABASE_CONSTANTS.RESOURCE_URL + ", " +
 					"r." + DATABASE_CONSTANTS.RESOURCE_TYPE + ", " +
					"count(" + DATABASE_CONSTANTS.USERTAGGROUPRESOURCE_RESOURCEID + ") " + 
				" FROM " + DATABASE_CONSTANTS.TABLE_USERTAGGROUPRESOURCE + " as tas, " + DATABASE_CONSTANTS.TABLE_RESOURCE + " as r " +
				" WHERE tas." + DATABASE_CONSTANTS.USERTAGGROUPRESOURCE_TAGID + " = '" + tag.getId() + "'" +				 												
						" AND tas." + DATABASE_CONSTANTS.USERTAGGROUPRESOURCE_RESOURCEID + " = r." +  DATABASE_CONSTANTS.RESOURCE_ID +
				" group by tas." + DATABASE_CONSTANTS.USERTAGGROUPRESOURCE_RESOURCEID + 
				" order by count(tas." + DATABASE_CONSTANTS.USERTAGGROUPRESOURCE_RESOURCEID + ") DESC";
		GetWeightedResource scoringQuery = new GetWeightedResource(ObjectFactory.ds, query);
		Iterator iter = scoringQuery.execute().iterator();
		List<WeightedResource> resources = null;
		if (iter.hasNext()){
			resources = (List<WeightedResource>) iter.next();
		}
		
		//resources contained in directly tagged groups:
		query = 
			"SELECT " + "gr." + DATABASE_CONSTANTS.GROUPRESOURCE_RESOURCE_ID + ", " +
			 			"r." + DATABASE_CONSTANTS.RESOURCE_NAME + ", " +
			 			"r." + DATABASE_CONSTANTS.RESOURCE_DESCRIPTION + ", " +
			 			"r." + DATABASE_CONSTANTS.RESOURCE_URL + ", " +
			 			"r." + DATABASE_CONSTANTS.RESOURCE_TYPE + ", " +
					"count(gr." + DATABASE_CONSTANTS.GROUPRESOURCE_RESOURCE_ID + ") " + 
				" FROM " + DATABASE_CONSTANTS.TABLE_GROUPRESOURCE + " as gr, " +
							DATABASE_CONSTANTS.TABLE_RESOURCE + " as r " +
				" WHERE gr." + DATABASE_CONSTANTS.GROUPRESOURCE_GROUP_ID + " in " +
					" ( SELECT distinct " + DATABASE_CONSTANTS.USERTAGGROUPRESOURCE_RESOURCEID + " FROM " + DATABASE_CONSTANTS.TABLE_USERTAGGROUPRESOURCE + 
				" WHERE " + DATABASE_CONSTANTS.USERTAGGROUPRESOURCE_TAGID + " = '" + tag.getId() + "' AND " +
						    DATABASE_CONSTANTS.USERTAGGROUPRESOURCE_RESOURCEID + " in " +
							"(SELECT " + DATABASE_CONSTANTS.RESOURCE_ID + " FROM " + DATABASE_CONSTANTS.TABLE_RESOURCE  + 
								" WHERE " + DATABASE_CONSTANTS.RESOURCE_TYPE + " = '" +  MODEL_CONSTANTS.RESOURCE_TYPE_GROUP + "')" + 
								" group by " + DATABASE_CONSTANTS.USERTAGGROUPRESOURCE_RESOURCEID + ") AND " +
						" gr." + DATABASE_CONSTANTS.GROUPRESOURCE_RESOURCE_ID + " = r." +  DATABASE_CONSTANTS.RESOURCE_ID +
				" group by gr." + DATABASE_CONSTANTS.GROUPRESOURCE_RESOURCE_ID + 
				" order by count(gr." + DATABASE_CONSTANTS.GROUPRESOURCE_RESOURCE_ID + ") DESC";
		scoringQuery = new GetWeightedResource(ObjectFactory.ds, query);
		iter = scoringQuery.execute().iterator();
		List<WeightedResource> groupResources = null;
		if (iter.hasNext()){
			groupResources = (List<WeightedResource>) iter.next();
		}
		
		
		//number of resources that are directly tagged with tag and contained in group X:
		query = 
			"SELECT gr." + DATABASE_CONSTANTS.GROUPRESOURCE_GROUP_ID + ", " +
 					"r." + DATABASE_CONSTANTS.RESOURCE_NAME + ", " +
 					"r." + DATABASE_CONSTANTS.RESOURCE_DESCRIPTION + ", " +
 					"r." + DATABASE_CONSTANTS.RESOURCE_URL + ", " +
 					"r." + DATABASE_CONSTANTS.RESOURCE_TYPE + ", " +
					"count(" + DATABASE_CONSTANTS.GROUPRESOURCE_RESOURCE_ID + ") " + 
				" FROM " + DATABASE_CONSTANTS.TABLE_GROUPRESOURCE + " as gr, " +
						DATABASE_CONSTANTS.TABLE_RESOURCE + " as r " +
				" WHERE gr." + DATABASE_CONSTANTS.GROUPRESOURCE_RESOURCE_ID + " in " +
						"(SELECT distinct " + DATABASE_CONSTANTS.USERTAGGROUPRESOURCE_RESOURCEID + " FROM " + DATABASE_CONSTANTS.TABLE_USERTAGGROUPRESOURCE  + 
						" WHERE " + DATABASE_CONSTANTS.USERTAGGROUPRESOURCE_TAGID + " = '" +  tag.getId() + "') " +
						" AND gr." + DATABASE_CONSTANTS.GROUPRESOURCE_RESOURCE_ID + " = r." +  DATABASE_CONSTANTS.RESOURCE_ID +
				" group by gr." + DATABASE_CONSTANTS.GROUPRESOURCE_GROUP_ID + 
				" order by count(gr." + DATABASE_CONSTANTS.GROUPRESOURCE_RESOURCE_ID + ") DESC";
		 scoringQuery = new GetWeightedResource(ObjectFactory.ds, query);
		iter = scoringQuery.execute().iterator();
		List<WeightedResource> groupsWithTaggedResources = null;
		if (iter.hasNext()){
			groupsWithTaggedResources = (List<WeightedResource>) iter.next();
		}

			
		//number of resources that are directly tagged with tag and grouped with resource X:
		query = 
				"Select  r.id, r.name, r.description, r.url, r.type, sum(score.weight) FROM " +
				" Resource as r, " +
				" (Select gr.groupId as gId, count(*) as weight FROM groupResource as gr, userTagGroupResource as tas WHERE gr.groupId = tas.groupId AND gr.resourceId = tas.resourceId AND tas.tagId = " + tag.getId() + "  group by gr.groupId) as score, " +
				" groupResource as member WHERE r.id = member.resourceId AND member.groupId = score.gId AND r.id not in (Select resourceId from userTagGroupResource where tagId = " + tag.getId() + ") group by r.id order by score.weight desc";
		 scoringQuery = new GetWeightedResource(ObjectFactory.ds, query);
		iter = scoringQuery.execute().iterator();
		List<WeightedResource> resourcesGroupedWithTaggedResources = null;
		if (iter.hasNext()){
			resourcesGroupedWithTaggedResources = (List<WeightedResource>) iter.next();
		}

	
		//compute scoring:
		//directly tagged:
		if(resources != null){ 
			for(WeightedResource res: resources){
				resources.get(resources.indexOf(res)).setWeight(res.getWeight() * SEARCH_STRATEGY_CONSTANTS.DIRECT_WEIGHT);
			}
		}else{
			resources = new ArrayList<WeightedResource>();
		}
		//group tag propagation:
		if(groupResources != null){
			for(WeightedResource res: groupResources){
				if(resources.contains(res)){
					resources.get(resources.indexOf(res)).setWeight(resources.get(resources.indexOf(res)).getWeight() + res.getWeight() * SEARCH_STRATEGY_CONSTANTS.GROUP_CONTEXT_WEIGHT);
				}else{
					res.setWeight(res.getWeight() * SEARCH_STRATEGY_CONSTANTS.GROUP_CONTEXT_WEIGHT);
					resources.add(res);
				}
			}
		}
		//resources propagate their tags to the group:
		if(groupsWithTaggedResources != null){
			for(WeightedResource res: groupsWithTaggedResources){
				if(resources.contains(res)){
					resources.get(resources.indexOf(res)).setWeight(resources.get(resources.indexOf(res)).getWeight() + res.getWeight() * SEARCH_STRATEGY_CONSTANTS.RESOURCES_IN_GROUP_WEIGHT);
				}else{
					res.setWeight(res.getWeight() * SEARCH_STRATEGY_CONSTANTS.RESOURCES_IN_GROUP_WEIGHT);
					resources.add(res);
				}
			}
		}
		//resources propagate their tags to their neighbour resources:
		if(resourcesGroupedWithTaggedResources != null){
			for(WeightedResource res: resourcesGroupedWithTaggedResources){
				if(resources.contains(res)){
					resources.get(resources.indexOf(res)).setWeight(resources.get(resources.indexOf(res)).getWeight() + res.getWeight() * SEARCH_STRATEGY_CONSTANTS.RESOURCE_NEIGHBOUR_WEIGHT);
				}else{
					res.setWeight(res.getWeight() * SEARCH_STRATEGY_CONSTANTS.RESOURCE_NEIGHBOUR_WEIGHT);
					resources.add(res);
				}
			}
		}
		Collections.sort(resources, new WeightedIdentifiableComparatorByWeight());
		return resources;
	}

	/* (non-Javadoc)
	 * @see net.groupme.search.strategy.RankingStrategy#rankTags(net.groupme.search.strategy.model.RankingPreferences)
	 */
	public List<WeightedTag> rankTags(RankingPreferences preferences) {
		//TODO: dummy implementation (weights like SEARCH_STRATEGY_CONSTANTS.RESOURCES_IN_GROUP_WEIGHT are not considered)
		List<WeightedResource> resources = rankResources(preferences);
		String possibleResourceIDs = null;
		for (WeightedResource weightedResource : resources) {
			if(possibleResourceIDs == null){
				possibleResourceIDs = " tas.resourceId = " + weightedResource.getIdentifiable().getId();
			}else{
				possibleResourceIDs += " OR tas.resourceId = " + weightedResource.getIdentifiable().getId();
			}
		}
		if(possibleResourceIDs == null){
			return new ArrayList<WeightedTag>();
		}
		String query = "Select t.id, t.keyword, count(*) as n FROM userTagGroupResource tas, Tag t WHERE t.id = tas.tagId AND (" + possibleResourceIDs + ") group by tas.tagId order by n desc";
		Statement sqlStatement = null;
		ResultSet tagsAndAmount = null;
		List<WeightedTag> cloud = new ArrayList<WeightedTag>();
		try {
			sqlStatement = ObjectFactory.ds.getConnection().createStatement();
			
//			Tags, die der Resource assigned sind:
			tagsAndAmount = sqlStatement.executeQuery(query);
			while(tagsAndAmount.next()){
				WeightedTag wt = new WeightedTag(ObjectFactory.getTagByIdKeyWordAndDescription(tagsAndAmount.getString(1), tagsAndAmount.getString(2), tagsAndAmount.getString(2)),  tagsAndAmount.getDouble(3));
				cloud.add(wt);
			}
		} catch (SQLException e) {
			logger.error("Problem while querying DB.", e);
		}
		return cloud;
	}

	/* (non-Javadoc)
	 * @see net.groupme.search.strategy.RankingStrategy#rankUsers(net.groupme.search.strategy.model.RankingPreferences)
	 */
	public List<WeightedUser> rankUsers(RankingPreferences preferences) {
		//TODO: dummy implementation (weights like SEARCH_STRATEGY_CONSTANTS.RESOURCES_IN_GROUP_WEIGHT are not considered)
		List<WeightedResource> resources = rankResources(preferences);
		String possibleResourceIDs = null;
		for (WeightedResource weightedResource : resources) {
			if(possibleResourceIDs == null){
				possibleResourceIDs = " tas.resourceId = " + weightedResource.getIdentifiable().getId();
			}else{
				possibleResourceIDs += " OR tas.resourceId = " + weightedResource.getIdentifiable().getId();
			}
		}
		
		String query = "Select u.id, u.username, count(*) as n FROM userTagGroupResource tas, User u WHERE u.id = tas.userId AND (" + possibleResourceIDs + ") group by tas.userId order by n desc";
		if(possibleResourceIDs == null){
			return new ArrayList<WeightedUser>();
		}
		Statement sqlStatement = null;
		ResultSet userAndAmount = null;
		List<WeightedUser> cloud = new ArrayList<WeightedUser>();
		try {
			sqlStatement = ObjectFactory.ds.getConnection().createStatement();
			
			userAndAmount = sqlStatement.executeQuery(query);
			WeightedUser wt = null;
			while(userAndAmount.next()){
				try {
					wt = new WeightedUser(ObjectFactory.getUser(userAndAmount.getInt(1)),  userAndAmount.getDouble(3));
					cloud.add(wt);
				} catch (UserNotExistingException e) {
					e.printStackTrace();
				}
			}
		} catch (SQLException e) {
			logger.error("Problem while querying DB.", e);
		}
		return cloud;
	}

//	@Test
//	public void testen(){
//		PreferencesInTags p = new PreferencesInTags();
//		p.add(ObjectFactory.getTag("groupme"));
//		System.out.println("\nUser:");
//		List<WeightedUser> users = rankUsers(p);
//		for (WeightedUser weightedUser : users) {
//			System.out.println(weightedUser);
//		}
//		System.out.println("\nTAGS:");
//		List<WeightedTag> tags = rankTags(p);
//		for (WeightedTag weightedTag : tags) {
//			System.out.println(weightedTag);
//		}
//		System.out.println("\nResources:");
//		List<WeightedResource> res = rankResources(p);
//		for (WeightedResource weightedTag : res) {
//			System.out.println(weightedTag);
//		}
//	}
}
